package com.commons.dbutils.dao;

import java.lang.reflect.Field;


import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.commons.dbutils.util.SQLUtils;
import com.commons.dbutils.util.VoClassUtils;


public class DaoRunner implements IDAO {
	
	private Runner sqlRunner = null;
	private DataSource dataSource;
	public DaoRunner(){	 
		this.sqlRunner = new SqlRunner(0,0,0);
	}
	
	/**
	 * 设置crud的超时时间
	 * @param ds
	 * @param readTimeOut
	 * @param insertOrUpdateTimeOut
	 * @param batchTimeOut
	 */
	public DaoRunner(int readTimeOut,int insertOrUpdateTimeOut,int batchTimeOut){
		this.sqlRunner = new SqlRunner(readTimeOut,insertOrUpdateTimeOut,batchTimeOut);
	}
	
	private Connection getConnection() throws SQLException{
		if(this.dataSource==null){
			throw new SQLException("dataSource is null.");
		}
		Connection connection =this.dataSource.getConnection();
		if(connection==null ){
			throw new SQLException("Connection is null.");
		}
		
		return connection;
	}
	
	@Override
	public <T> List<T> getListBySQL(Class<T> className, String querySQL,
			Object[] paramArrayOfObject) throws Exception {
		if(className==null || querySQL==null || querySQL.length()<1){
			return null;
		}
		Connection conn = null;
		try{
			conn = this.getConnection();
			return this.sqlRunner.query(conn,querySQL,paramArrayOfObject, new BeanListHandler<T>(className));
		}catch (Exception e) {
			throw e;
		}finally{
			release(conn);
		}
		
	}

	@Override
	public int execBySQL(String paramString, Object[] paramArrayOfObject)
			throws Exception {
		if(paramString==null || paramString.length()<1){
			return -1;
		}
		Connection conn = null;
		try{
			conn = this.getConnection();
			return this.sqlRunner.update(conn,paramString, paramArrayOfObject);
		}catch (Exception e) {
			throw e;
		}finally{
			release(conn);
		}
		
	}

	@Override
	public long getCountBySQL(String paramString, Object[] paramArrayOfObject)
			throws Exception {
		 if(paramString==null || paramString.length()<1){
		 	 return 0;
		 }
		 long total =0;
		 Map<String, Object> map = null;  
		 Connection conn = null;
		 try{
			 conn = this.getConnection();
			 map = this.sqlRunner.query(conn,paramString,paramArrayOfObject, new MapHandler());  
		 }catch (Exception e) {
			 throw e;
		 }finally{
			 release(conn);
		 }
		 
		 if(map!=null && map.size()>0){
			 for(String key:map.keySet()){
				 total = new Long(String.valueOf(map.get(key)));
				 break;
			 }
		 }

		 return total;
	}

	@Override
	public  List<Map<String,Object>> customSql(String paramString) throws Exception {
		if(paramString==null || paramString.length()<1){
			return null;
		}
		Connection conn = null;
		try{
			conn = this.getConnection();
			return this.sqlRunner.query(conn,paramString, new MapListHandler());
		}catch (Exception e) {
			throw e;
		}finally{
			release(conn);
		}
		
	}



	@Override
	public <T> int insert(T entity) throws Exception {
		if(entity==null){
			throw new Exception("insert table entity is null.");
		}
		Class<?> clazz  = entity.getClass();
		List<Field> columnList = VoClassUtils.getInsertableFields(clazz);
		String dbColumn = null;
		if(columnList==null || columnList.size()<1){
			throw new SQLException(new StringBuffer(clazz.getName()).append(" not found insert columns.").toString());
		}
		StringBuffer buffer = new StringBuffer();
		buffer.append(SQLUtils.INSERT);
		buffer.append(VoClassUtils.getTableName(clazz));
		buffer.append(SQLUtils.LEFT_BRACKET);
		Field f= null;
		Method[] mArr = new Method[columnList.size()];
		for(int fnum=0;fnum<columnList.size();fnum++){
			f=columnList.get(fnum);
			dbColumn = VoClassUtils.getDBCloumnName(clazz, f);
			if(dbColumn==null || dbColumn.length()<1){
				throw new SQLException(new StringBuffer(clazz.getName()).append(" property ").append(f.getName()).append(" not found  column name.").toString());
			}
			buffer.append(dbColumn);
			if((fnum+1)!=columnList.size()){
				buffer.append(SQLUtils.COMMA);
			}
			
			mArr[fnum] = VoClassUtils.getGetterMethod(clazz, f);
			if(mArr[fnum]==null){
				throw new SQLException(new StringBuffer(clazz.getName()).append(" property ").append(f.getName()).append(" not found  getter Method .").toString());
			}
			
			
		}
		buffer.append(SQLUtils.RIGHT_BRACKET);
		buffer.append(SQLUtils.VALUES);
		buffer.append(SQLUtils.LEFT_BRACKET);
		buffer.append(SQLUtils.bulidPreparedStatement(columnList.size()));
		buffer.append(SQLUtils.RIGHT_BRACKET);
		Object[] objects = new Object[mArr.length];
	
		for(int mNum=0;mNum<mArr.length;mNum++){
			objects[mNum]=mArr[mNum].invoke(entity, new Object[0]);
		}
		Connection conn = null;
		try{
			conn = this.getConnection();
			return this.sqlRunner.update(conn,buffer.toString(), objects);
		}catch (Exception e) {
			throw e;
		}finally{
			release(conn);
		}
		
		
	}

	@Override
	public <T> int update(T entity) throws Exception {
		Class<?> className = entity.getClass();
		List<Field> fList = VoClassUtils.getIdFields(className);
		if(fList==null){
			throw new SQLException(new StringBuffer(className.getName()).append(" not found id.").toString());
		}
		if(fList.size()>1){
			throw new SQLException(new StringBuffer(className.getName()).append(" id more than one.").toString());
		}
		
		Method method = VoClassUtils.getGetterMethod(className, fList.get(0));
		
		return this.updateById(entity,method.invoke(entity, new Object[0]));
	}

	@Override
	public <T> int updateById(T entity, Object id)
			throws Exception {
		if(id==null ){
			throw new SQLException("id can not be null.");
		}
		Class<?> className = entity.getClass();
		List<Field> fList = VoClassUtils.getIdFields(className);
		if(fList==null){
			throw new SQLException(new StringBuffer(className.getName()).append(" not found id.").toString());
		}
		if(fList.size()>1){
			throw new SQLException(new StringBuffer(className.getName()).append(" id more than one.").toString());
		}
		
		List<Field> ufList = VoClassUtils.getUpdatableFields(className);
		if(ufList==null || ufList.size()<1){
			throw new SQLException(new StringBuffer(className.getName()).append(" no has update fields.").toString());
		}
		
		Method method=null;
		Object[] params = new Object[ufList.size()+1];
		StringBuffer sql = new StringBuffer(SQLUtils.UPDATE);
		sql.append(VoClassUtils.getTableName(className));
		sql.append(SQLUtils.SET);
		Field uf=null;
		for(int num=0;num<ufList.size();num++){
			uf = ufList.get(num);
			method = VoClassUtils.getGetterMethod(className, uf);
			if(method==null){
				throw new SQLException(new StringBuffer(className.getName()).append(" field is ").append(uf.getName()).append(" not hava getter.").toString());
			}
			
			params[num]=method.invoke(entity, new Object[0]);
			
			sql.append(VoClassUtils.getDBCloumnName(className, uf));
			sql.append(SQLUtils.EQ);
			sql.append(SQLUtils.QUESTION);
			if((num+1)!=ufList.size()){
				sql.append(SQLUtils.COMMA);
			}
			
		}
		
		
		sql.append(SQLUtils.WHERE);
		sql.append(VoClassUtils.getDBCloumnName(className, fList.get(0)));
		sql.append(SQLUtils.EQ);
		sql.append(SQLUtils.QUESTION);
		
		params[ufList.size()]=id;
		Connection conn = null;
		try{
			conn = this.getConnection();
			return this.sqlRunner.update(conn,sql.toString(),params);
		}catch (Exception e) {
			throw e;
		}finally{
			release(conn);
		}
		
	}

	@Override
	public <T> int updateByColumns(T entity,String condition, Object[] value)
			throws Exception {
		if(condition==null  || condition.length()<1){
			throw new SQLException("condition can not be null and condition.length must be greater than zero.");
		}
		if(value==null  || value.length<1){
			throw new SQLException("value can not be null and value.length must be greater than zero.");
		}
		Class<?> className = entity.getClass();
		
		
		List<Field> ufList = VoClassUtils.getUpdatableFields(className);
		if(ufList==null || ufList.size()<1){
			throw new SQLException(new StringBuffer(className.getName()).append(" no has update fields.").toString());
		}
		
		Method method=null;
		Object[] params = new Object[ufList.size()+value.length];
		StringBuffer sql = new StringBuffer(SQLUtils.UPDATE);
		sql.append(VoClassUtils.getTableName(className));
		sql.append(SQLUtils.SET);
		Field uf=null;
		for(int num=0;num<ufList.size();num++){
			uf = ufList.get(num);
			method = VoClassUtils.getGetterMethod(className, uf);
			if(method==null){
				throw new SQLException(new StringBuffer(className.getName()).append(" field is ").append(uf.getName()).append(" not hava getter.").toString());
			}
			
			params[num]=method.invoke(entity, new Object[0]);
			
			sql.append(VoClassUtils.getDBCloumnName(className, uf));
			sql.append(SQLUtils.EQ);
			sql.append(SQLUtils.QUESTION);
			if((num+1)!=ufList.size()){
				sql.append(SQLUtils.COMMA);
			}
			
		}
		
		sql.append(SQLUtils.WHERE);
		sql.append(condition);
		
		
		for(int n=ufList.size(),i=0;n<params.length;n++,i++){
			params[n]=value[i];
		}
		Connection conn = null;
		try{
			conn = this.getConnection();
			return this.sqlRunner.update(conn,sql.toString(),params);
		}catch (Exception e) {
			throw e;
		}finally{
			release(conn);
		}
		
	}

	@Override
	public int deleteById(Class<?> className, Object id) throws Exception {
		if(id==null ){
			throw new SQLException("id can not be null.");
		}
		List<Field> fList = VoClassUtils.getIdFields(className);
		if(fList==null){
			throw new SQLException(new StringBuffer(className.getName()).append(" not found id.").toString());
		}
		if(fList.size()>1){
			throw new SQLException(new StringBuffer(className.getName()).append(" id more than one.").toString());
		}
		StringBuffer sql = new StringBuffer(SQLUtils.DELETE_FROM);
		sql.append(VoClassUtils.getTableName(className));
		sql.append(SQLUtils.WHERE);
		sql.append(VoClassUtils.getDBCloumnName(className, fList.get(0)));
		sql.append(SQLUtils.EQ);
		sql.append(SQLUtils.QUESTION);
		
		Connection conn = null;
		try{
			conn = this.getConnection();
			return this.sqlRunner.update(conn,sql.toString(),id);
		}catch (Exception e) {
			throw e;
		}finally{
			release(conn);
		}
	}

	@Override
	public int deleteByIDS(Class<?> className, Object[] ids) throws Exception {
		if(ids==null ||ids.length<1){
			throw new SQLException("ids can not be null and ids.length must be greater than zero.");
		}
		List<Field> fList = VoClassUtils.getIdFields(className);
		if(fList==null){
			throw new SQLException(new StringBuffer(className.getName()).append(" not found id.").toString());
		}
		if(fList.size()>1){
			throw new SQLException(new StringBuffer(className.getName()).append(" id more than one.").toString());
		}
		StringBuffer sql = new StringBuffer(SQLUtils.DELETE_FROM);
		sql.append(VoClassUtils.getTableName(className));
		sql.append(SQLUtils.WHERE);
		sql.append(VoClassUtils.getDBCloumnName(className, fList.get(0)));
		sql.append(SQLUtils.IN);
		sql.append(SQLUtils.LEFT_BRACKET);
		sql.append(SQLUtils.bulidPreparedStatement(ids.length));
		sql.append(SQLUtils.RIGHT_BRACKET);
		
		Connection conn = null;
		try{
			conn = this.getConnection();
			return this.sqlRunner.update(conn,sql.toString(),ids);
		}catch (Exception e) {
			throw e;
		}finally{
			release(conn);
		}
	}

	@Override
	public <T> T get(Class<T> className, Object id) throws Exception {
		
		if(id==null ){
			throw new SQLException("id can not be null.");
		}
		List<Field> fList = VoClassUtils.getIdFields(className);
		if(fList==null){
			throw new SQLException(new StringBuffer(className.getName()).append(" not found id.").toString());
		}
		if(fList.size()>1){
			throw new SQLException(new StringBuffer(className.getName()).append(" id more than one.").toString());
		}
		StringBuffer sql = new StringBuffer(SQLUtils.SELECT_ALL);
		sql.append(VoClassUtils.getTableName(className));
		sql.append(SQLUtils.WHERE);
		sql.append(VoClassUtils.getDBCloumnName(className, fList.get(0)));
		sql.append(SQLUtils.EQ);
		sql.append(SQLUtils.QUESTION);
		T t =null;
		Connection conn = null;
		try{
			conn = this.getConnection();
			t = this.sqlRunner.query(conn,sql.toString(),id, new BeanHandler<T>(className));
		}catch (Exception e) {
			throw e;
		}finally{
			release(conn);
		}
		
		return t;
	}

	@Override
	public <T> List<T> getListByIds(Class<T> className, Object[] ids)
			throws Exception {
		
		if(ids==null ||ids.length<1){
			throw new SQLException("ids can not be null and ids.length must be greater than zero.");
		}
		
		List<Field> fList = VoClassUtils.getIdFields(className);
		if(fList==null){
			throw new SQLException(new StringBuffer(className.getName()).append(" not found id.").toString());
		}
		if(fList.size()>1){
			throw new SQLException(new StringBuffer(className.getName()).append(" id more than one.").toString());
		}
		
		StringBuffer sql = new StringBuffer(SQLUtils.SELECT_ALL);
		sql.append(VoClassUtils.getTableName(className));
		sql.append(SQLUtils.WHERE);
		sql.append(VoClassUtils.getDBCloumnName(className, fList.get(0)));
		sql.append(SQLUtils.IN);
		sql.append(SQLUtils.LEFT_BRACKET);
		sql.append(SQLUtils.bulidPreparedStatement(ids.length));
		sql.append(SQLUtils.RIGHT_BRACKET);
		Connection conn = null;
		try{
			conn = this.getConnection();
			return this.sqlRunner.query(conn,sql.toString(),ids, new BeanListHandler<T>(className));
		}catch (Exception e) {
			throw e;
		}finally{
			release(conn);
		}
		
	}

	@Override
	public <T> T get(Class<T> className, String propertyName, Object value)
			throws Exception {
		if(propertyName==null || propertyName.length()<1  || value==null){
			throw new SQLException("propertyName and value must be not null.");
		}
		StringBuffer sql = new StringBuffer(SQLUtils.SELECT_ALL);
		sql.append(VoClassUtils.getTableName(className));
		sql.append(SQLUtils.WHERE);
		sql.append(propertyName);
		sql.append(SQLUtils.EQ);
		sql.append(SQLUtils.QUESTION);
		List<T> tList = null;
		Connection conn = null;
		try{
			conn = this.getConnection();
			tList = this.sqlRunner.query(conn,sql.toString(),value, new BeanListHandler<T>(className));
		}catch (Exception e) {
			throw e;
		}finally{
			release(conn);
		}
		return tList!=null && tList.size()>0 ?tList.get(0):null;
	}

	@Override
	public <T> List<T> getList(Class<T> className, String propertyName,
			Object value) throws Exception {
		if(propertyName==null|| propertyName.length()<1  || value==null){
			throw new SQLException("propertyName and value must be not null.");
		}
		StringBuffer sql = new StringBuffer(SQLUtils.SELECT_ALL);
		sql.append(VoClassUtils.getTableName(className));
		sql.append(SQLUtils.WHERE);
		sql.append(propertyName);
		sql.append(SQLUtils.EQ);
		sql.append(SQLUtils.QUESTION);
		Connection conn = null;
		try{
			conn = this.getConnection();
			return this.sqlRunner.query(conn,sql.toString(),value, new BeanListHandler<T>(className));
		}catch (Exception e) {
			throw e;
		}finally{
			release(conn);
		}
		
	}

	@Override
	public <T> List<T> getAll(Class<T> className) throws Exception {
		StringBuffer sql = new StringBuffer(SQLUtils.SELECT_ALL);
		sql.append(VoClassUtils.getTableName(className));
		Connection conn = null;
		try{
			conn = this.getConnection();
			return this.sqlRunner.query(conn,sql.toString(), new BeanListHandler<T>(className));
		}catch (Exception e) {
			throw e;
		}finally{
			release(conn);
		}
		
	}

	@Override
	public long getTotalCount(Class<?> className) throws Exception {
		StringBuffer sql = new StringBuffer(SQLUtils.SELECT_COUNT);
		sql.append(VoClassUtils.getTableName(className));
		Long count= null;
		Connection conn = null;
		try{
			conn = this.getConnection();
			count= (Long) this.sqlRunner.query(conn,sql.toString(),new ScalarHandler(SQLUtils.SELECT_TMP_COLUMN));
		}catch (Exception e) {
			throw e;
		}finally{
			release(conn);
		}
		return count!=null && count.longValue()>0?count.longValue():0;
	}

	@Override
	public long getTotalCount(Class<?> className,String param,Object[] value)
			throws Exception {
		
		StringBuffer sql = new StringBuffer(SQLUtils.SELECT_COUNT);
		sql.append(VoClassUtils.getTableName(className));
		sql.append(SQLUtils.WHERE);
		sql.append(param);
		Long count= null;
		Connection conn = null;
		try{
			conn = this.getConnection();
			count= (Long) this.sqlRunner.query(conn,sql.toString(), value,new ScalarHandler(SQLUtils.SELECT_TMP_COLUMN));
		}catch (Exception e) {
			throw e;
		}finally{
			release(conn);
		}
		return count!=null && count.longValue()>0?count.longValue():0;
		
	}

	

	@Override
	public boolean isExist(Class<?> className, String propertyName, Object value)
			throws Exception {
		if(propertyName==null ||  propertyName.length()<1 || value==null){
			return false;
		}
		StringBuffer sql = new StringBuffer(SQLUtils.SELECT_COUNT);
		sql.append(VoClassUtils.getTableName(className));
		sql.append(SQLUtils.WHERE);
		sql.append(propertyName);
		sql.append(SQLUtils.EQ);
		sql.append(SQLUtils.QUESTION);
		Long count= null;
		Connection conn = null;
		try{
			conn = this.getConnection();
			count= (Long) this.sqlRunner.query(conn,sql.toString(), value,new ScalarHandler(SQLUtils.SELECT_TMP_COLUMN));
		}catch (Exception e) {
			throw e;
		}finally{
			release(conn);
		}
		return count!=null && count.longValue()>0?true:false;
	}

	@Override
	public <T> List<T> getListByPage(Class<T> clazz,
			String condition,Object[] condition_values, String columns, int page,
			int pageSize, String orderBy) throws Exception {
		
		StringBuffer sql = new StringBuffer(SQLUtils.SELECT);
		
		if(columns==null || columns.length()<1){
			sql.append(SQLUtils.ALL_COLUMN);
		}else{
			sql.append(columns);
		}
		sql.append(SQLUtils.FROM);
		sql.append(VoClassUtils.getTableName(clazz));
		if(condition!=null && condition.length()>0){
			sql.append(SQLUtils.WHERE);
			sql.append(condition);
		}
		if(orderBy!=null && orderBy.length()>0){
			sql.append(SQLUtils.SORT);
			sql.append(orderBy);
		}
		if(page>0 && pageSize>0){
			sql.append(page);
			sql.append(SQLUtils.COMMA);
			sql.append(pageSize);
		}else{
			throw new SQLException(new StringBuffer("page and pageSize Must be greater than zero,page is")
									.append(page).append(",pageSize is").append(pageSize).toString());
		}
		Connection conn = null;
		try{
			conn = this.getConnection();
			return this.sqlRunner.query(conn,sql.toString(),condition_values, new BeanListHandler<T>(clazz));
		}catch (Exception e) {
			throw e;
		}finally{
			release(conn);
		}
		
	}
	
	private void release(Connection conn) throws SQLException{
		if (conn != null){
			conn.close();
		}
	}

	public DataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	

}
